﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_MNY_IN_OUT_MGT_Get_Monthly_Balance')
	BEGIN
		DROP Procedure usp_UPDMS_MNY_IN_OUT_MGT_Get_Monthly_Balance
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_MNY_IN_OUT_MGT_Get_Monthly_Balance
**	Desc : 월별잔액
**	Test Exec Query : Exec usp_UPDMS_MNY_IN_OUT_MGT_Get_Monthly_Balance '2010-07'
**	Called by : Mny_Dac_UPDMS_MNY_IN_OUT_MGT.cs
**	Program ID : Mny4011b
**	Auth : 송시명
**	Date : 2010-07-30
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_MNY_IN_OUT_MGT_Get_Monthly_Balance]
@ls_base_ym nvarchar(7)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT CONVERT(INT, ISNULL(SUM(Amount), 0)) AS Amount
  FROM (
       SELECT CASE t.Class WHEN 'AA001' THEN SUM(t.Amount) * 1 --수입
                           WHEN 'AA002' THEN SUM(t.Amount) * -1 --지출
                           WHEN 'AA003' THEN SUM(t.Amount) * -1 --저축
              END Amount
         FROM (
              --현금
              SELECT LEFT(umiom.Reg_Dt, 7) AS Reg_Dt, 
                     umiom.Class, SUM(ISNULL(umiom.Amount, 0)) AS Amount
                FROM UPDMS_MNY_IN_OUT_MGT umiom WITH(NOLOCK)
               WHERE LEFT(umiom.Reg_Dt, 7) = @ls_base_ym 
               GROUP BY LEFT(umiom.Reg_Dt, 7), umiom.Class
              UNION ALL
              --카드
              SELECT Reg_Dt,
                     'AA002', --지출만
                     ISNULL(SUM(CONVERT(MONEY, crd.Amount)), 0)
                FROM (
                     SELECT LEFT(CONVERT(VARCHAR, c.Paymnt_Dt), 7) AS Reg_Dt,
                            SUM(ISNULL(c.Amount, 0)) AS Amount
                       FROM UPDMS_CRD_ITEM_BIZ c WITH(NOLOCK)
                      WHERE LEFT(c.Paymnt_Dt, 7) = @ls_base_ym
                      GROUP BY LEFT(CONVERT(VARCHAR, c.Paymnt_Dt), 7)
                     UNION ALL
                     SELECT LEFT(CONVERT(VARCHAR, ucir.Paymnt_Dt), 7),
                            SUM(ISNULL(ucir.Ins_Month_Amt, 0))
                       FROM UPDMS_CRD_INSTALLMENT_REPAY ucir WITH(NOLOCK)
                      WHERE LEFT(ucir.Paymnt_Dt, 7) = @ls_base_ym
                      GROUP BY LEFT(CONVERT(VARCHAR, ucir.Paymnt_Dt), 7)
                     ) crd
               GROUP BY crd.Reg_Dt
              ) t
        GROUP BY 'M' + RIGHT(t.Reg_Dt, 2), t.Class
       ) x

GO